package sgc.beans;

import java.io.IOException;
import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.faces.component.html.HtmlDataTable;
import javax.faces.context.FacesContext;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletContext;
import javax.sql.DataSource;

import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import net.sf.jasperreports.view.JasperViewer;

import sgc.struc.Company;
import sgc.struc.Pord_Header;
import sgc.struc.Pord_Position;
import sgc.struc.Req_Header;
import sgc.struc.Suplier;

public class PurchaseOrderListBean implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private Pord_Header header;
	private List<Pord_Header> headers;
	private List<Pord_Header> filter_headers;
	private List<Pord_Position> positions;
	private DataSource ds;
	private int company_code;
	private int suplier_code;
	private String company_name;
	private Company company;
	private Suplier suplier;

	public PurchaseOrderListBean() {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/sgcweb");
		} catch (NamingException e) {
			e.printStackTrace();
		}
		header = new Pord_Header();
		positions = new ArrayList<Pord_Position>();

		FacesContext context = FacesContext.getCurrentInstance();
		CheckUserBean cu = (CheckUserBean) context.getApplication()
				.evaluateExpressionGet(context, "#{checkUserBean}",
						CheckUserBean.class);

		company_code = cu.getUsuario().getCompany_code();
		company_name = cu.getUsuario().getCompany();
	}

	public List<Pord_Header> getHeaders() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getAllPorHeader(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company_code);
		cs.execute();
		ResultSet result = cs.getResultSet();

		List<Pord_Header> list = new ArrayList<Pord_Header>();

		while (result.next()) {
			Pord_Header aux_header = new Pord_Header();
			aux_header.setPor_no(result.getInt(2));
			aux_header.setCecos_name(result.getString("cecos_cod") + " - "
					+ result.getString("cecos_descrip"));
			aux_header.setFec_por(result.getDate("fec_por"));
			aux_header.setFec_req(result.getDate("fec_req"));
			aux_header.setPor_deta(result.getString("detail"));
			aux_header.setPor_no(result.getInt("por_no"));
			aux_header.setPreq_no(result.getInt("req_no"));
			aux_header.setSolicitante_name(result.getString("user"));
			aux_header.setStatus_name(result.getString("status_name"));
			aux_header.setSuplier_code(result.getInt("sup_code"));
			aux_header.setSuplier_name(result.getInt("sup_code") + " - "
					+ result.getString("sup_name3") + " "
					+ result.getString("sup_name4") + ", "
					+ result.getString("sup_name1") + " "
					+ result.getString("sup_name2"));
			list.add(aux_header);
			suplier_code = result.getInt("sup_code");
		}
		con.close();

		return list;
	}

	public void setHeaders(List<Pord_Header> headers) {
		this.headers = headers;
	}

	public Pord_Header getHeader() {
		return header;
	}

	public void setHeader(Pord_Header header) {
		this.header = header;
	}

	private void getCompany() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getCompany(?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company_code);
		cs.execute();
		ResultSet result = cs.getResultSet();
		positions.clear();
		while (result.next()) {
			company = new Company();
			company.setAddress_ap(result.getString("address_ap"));
			company.setAddress_fl(result.getString("address_fl"));
			company.setAddress_no(result.getString("address_no"));
			company.setAddress_st(result.getString("address_st"));
			company.setCity(result.getString("city"));
			company.setCode(result.getInt("code"));
			company.setCountry(result.getString("country"));
			company.setName(result.getString("name"));
			company.setPostal_code(result.getString("postal_code"));
			company.setStart_date(result.getDate("start_date"));
			company.setState(result.getString("state"));
			company.setTax_id_1(result.getString("tax_id_1"));
			company.setTax_id_2(result.getString("tax_id_2"));
			company.setTax_type_1(result.getString("tax_type_1"));
			company.setTax_type_2(result.getString("tax_type_2"));
		}

	}

	private void getPositions() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getPorPosition(?, ?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company_code);
		cs.setInt(2, header.getPor_no());
		cs.execute();
		ResultSet result = cs.getResultSet();
		positions.clear();
		while (result.next()) {
			Pord_Position aux_pos = new Pord_Position();

			aux_pos.setPor_no(result.getInt("por_no"));
			aux_pos.setPor_pos(result.getInt("por_pos"));
			aux_pos.setMat_cant(result.getDouble("mat_cant"));
			aux_pos.setMat_code(result.getInt("mat_code"));
			aux_pos.setMat_name(result.getInt("mat_code") + " - "
					+ result.getString("mat_name"));
			aux_pos.setMat_pend(result.getDouble("mat_pend"));
			aux_pos.setMat_unit(result.getString("unit"));
			positions.add(aux_pos);
		}
	}

	private void getSuplier() throws SQLException {
		if (ds == null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
		if (con == null)
			throw new SQLException("Can't get database connection");

		String simpleProc = "{ CALL sp_getSuplier(?,?) }";
		CallableStatement cs = con.prepareCall(simpleProc);
		cs.setInt(1, company_code);
		cs.setInt(2, suplier_code);
		cs.execute();
		ResultSet result = cs.getResultSet();
		positions.clear();
		while (result.next()) {
			suplier = new Suplier();
			suplier.setAddress_ap(result.getString("address_ap"));
			suplier.setAddress_fl(result.getString("address_fl"));
			suplier.setAddress_no(result.getString("address_no"));
			suplier.setAddress_st(result.getString("address_st"));
			suplier.setCity(result.getString("city"));
			suplier.setCode(suplier_code);
			suplier.setCompany(result.getString("company"));
			suplier.setCountry(result.getString("country"));
			suplier.setMail(result.getString("mail"));
			suplier.setName1(result.getString("name1"));
			suplier.setName2(result.getString("name2"));
			suplier.setName3(result.getString("name3"));
			suplier.setName4(result.getString("name4"));
			suplier.setPhone(result.getString("phone"));
			suplier.setPostal_code(result.getString("postal_code"));
			suplier.setStart_date(result.getDate("start_date"));
			suplier.setState(result.getString("state"));
			suplier.setTax_id_1(result.getString("tax_id_1"));
			suplier.setTax_id_2(result.getString("tax_id_2"));
			suplier.setTax_type_1(result.getString("tax_type_1"));
			suplier.setTax_type_2(result.getString("tax_type_2"));
		}

	}

	private void printInternal() throws JRException {

		
		DecimalFormat company_f = new DecimalFormat("0000");
		DecimalFormat porder_f = new DecimalFormat("000000000");

		String barcode = company_f.format(company.getCode())
				+ porder_f.format(header.getPor_no());
		
		ServletContext ctx = (ServletContext) FacesContext.getCurrentInstance()
				.getExternalContext().getContext();
		String sourceFileName = ctx
				.getRealPath("/reports/PurchaseOrder.jasper");
		String outputFileName = ctx.getRealPath("/private/" + company_code)
				+ "/PORD_" + company_code + "_" + header.getPor_no() + ".pdf";
		JRBeanCollectionDataSource dataSource = new JRBeanCollectionDataSource(
				positions);
		HashMap parameterMap = new HashMap();
		parameterMap.put("company", company);
		parameterMap.put("order_header", header);
		parameterMap.put("suplier", suplier);
		parameterMap.put("barcode", barcode);

		JasperPrint print = JasperFillManager.fillReport(sourceFileName,
				parameterMap, dataSource);
		JasperExportManager.exportReportToPdfFile(print, outputFileName);
		JasperViewer.viewReport(print, false);

	}

	public String print() throws SQLException, JRException {
		getCompany();
		getSuplier();
		getPositions();
		printInternal();
		return "OK";
	}

	public void inicio() throws IOException {
		FacesContext.getCurrentInstance().getExternalContext()
				.redirect("navegacion.xhtml");
	}

	public void logout() throws IOException {
		FacesContext.getCurrentInstance().getExternalContext()
				.invalidateSession();
	}

}
